# coding=utf-8

from framework.db.mysql import BaseDb
from tornado import gen
from lib import log
import time

class Index(BaseDb):

    @gen.coroutine
    def select_staff(self, Fschool_id, Fstudent_id, page, offset,Fposition,Fdorm_id):
        qurey = '''SELECT a.Fid, a.Fname, a.Fage, a.Fhire_date
                , a.Ftel, a.Favater, a.Fposition, c.Fdorm_name, a.Fdorm_id
                , a.Fcreate_time, b.Fid status
                FROM tb_staff a 
                LEFT JOIN tb_thumb_log b 
                ON a.Fid = b.Fcrew_id 
                AND b.Fstudent_id = %s
                LEFT JOIN tb_dorm c 
                ON a.Fdorm_id = c.Fid 
                WHERE a.Fschool_id = %s AND a.Fposition= %s AND a.Fdorm_id=%s 
                LIMIT %s, %s'''

        count_sql = 'SELECT count(*) FROM tb_staff WHERE Fschool_id = %s AND Fposition=%s AND Fdorm_id=%s'
        
        ret = yield self.executejson(qurey, (Fstudent_id, Fschool_id, Fposition,Fdorm_id,page, offset))
        count = yield self.execute(count_sql, (Fschool_id,Fposition,Fdorm_id))
        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def set_status(self, Fstudent_id, Fcrew_id, Fschool_id):
        qurey = 'SELECT count(Fid) FROM tb_thumb_log WHERE Fstudent_id = %s AND Fcrew_id = %s AND Fschool_id = %s'
        update = 'DELETE FROM tb_thumb_log WHERE Fstudent_id = %s AND Fcrew_id = %s AND Fschool_id = %s'

        t = -1
        count = yield self.execute(qurey, (Fstudent_id, Fcrew_id, Fschool_id))
        if count[0][0] > 0:
            yield self.upsert_commit(update, (Fstudent_id, Fcrew_id, Fschool_id))
        else:
            data = {
                "Fstudent_id":Fstudent_id,
                "Fcrew_id":Fcrew_id,
                "Fschool_id":Fschool_id,
                "Fcreate_time":time.time()
            }
            t = yield self.insert('tb_thumb_log', data)
            yield self.commit()
        raise gen.Return(t)